package com.zcsy.commons.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import jxl.Sheet;
import jxl.Workbook;

import org.apache.log4j.Logger;

/**  
 * @Project: hsdl
 * @Title: ScheduleUtils.java
 * @Package com.hsyg.operate.action
 * @Description: Excel文件导入工具类
 *
 * @author hujie wrihujie@foxmail.com
 * @date 2012-8-29 下午08:16:25
 * @Copyright: 2012 武汉虹翼信息有限公司  All rights reserved.
 * @version V1.0  
 */
public class XlsImportUtils {
	
	private static Logger logger = Logger.getLogger(XlsImportUtils.class.getSimpleName());
	
	/**
	 * Excel解析方法，用于解析Excel文本，文本必须是系统规定的班次导入模板，可在导入页面下载
	 * 
	 * @param is 待解析的文本输入流
	 * @param column 解析数据的列数
	 * @param begin 从第几行开始解析
	 * @param abandon 抛弃末位的几行
	 * 
	 * @author List 存储解析后数据的List
	 * 解析后返回的数据格式为List，list中包含的元素为ArrayList<String>
	 * list----------------
	 * 			| ArrayList<String>  包含excel中一行的数据，其中第一个元素为考题类型
	 * 			| ArrayList<String>
	 * 			| ArrayList<String>
	 * 			.
	 * 			.
	 * */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public static List parseXlsToString(InputStream is, int column, int begin, int abandon) throws Exception {
		
		logger.info("begin enter XlsImportUtils parseXlsToString methods.....");
		try {
			Workbook rwb = Workbook.getWorkbook(is);
			//int sheetNum = rwb.getNumberOfSheets();
			
			List result = new ArrayList();
			Sheet sheet = rwb.getSheet(0);
			if (sheet.getColumns() == column) {
				// 获取所有行
				for (int i = begin; i < (sheet.getRows() - abandon); i++) {
					ArrayList<String> rowList = new ArrayList<String>();
					// 第一个元素存储行号
					//rowList.add(Integer.toString(i + 1));
					
					// 解析具体的一行
					for (int j = 0; j < column; j++) {
						String content = sheet.getCell(j, i).getContents();
						rowList.add(content);
					}
					result.add(rowList);
				}
			}else{
				logger.error("导入文件列数应为："+column+"列，实际列数为："+sheet.getColumns()+"列");
			}
			return result;
		} catch (Exception e) {
			e.printStackTrace(); 
			throw new Exception();
		}
	}
	
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public static void main(String[] args) {
		InputStream is;
		try {
			is = new FileInputStream(new File("d:/朵儿网学生导入模版.xls"));
			List<List> list = parseXlsToString(is, 19, 1, 0);
			for (int i = 0; i < list.size(); i++) {
				List obj = list.get(i);
				String className = (String) obj.get(0);
				String babyNo = (String)obj.get(2);
				System.out.println("className="+className+", babyNo="+babyNo);
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
